package com.osfw.dbadminer.util;

import com.osfw.dbadminer.enums.DbOperType;
import com.osfw.dbadminer.enums.SQLStmtType;
import dm.jdbc.driver.DmDriver;

import java.sql.Driver;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicReference;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SQLParserUtils {

    public static String removeComments(String sql) {
        // 去除单行注释
        sql = sql.replaceAll("-- .*", "");

        // 去除多行注释
        sql = sql.replaceAll("/\\*.*?\\*/", "");

        return sql;
    }

    public static String removeWhitespace(String sql) {
        // 去除空格和回车
        sql = sql.replaceAll("\\s+", " ");

        return sql.trim();
    }

    public static String getFirstKeyword(String sql) {
        String firstKeyword = "";

        // 定义匹配SQL关键字的正则表达式
        String regex = "(?i)\\b(\\w+)\\b";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sql.trim());

        // 查找第一个关键字
        if (matcher.find()) {
            firstKeyword = matcher.group();
        }

        return firstKeyword;
    }

    public static boolean isQueryStatement(String sql) {
        sql = removeComments(sql);
        sql = removeWhitespace(sql);
        sql = getFirstKeyword(sql);

        // 判断是否为查询语句，检查第一个关键字是否匹配
        Pattern pattern = Pattern.compile("^(SELECT|WITH)");
        Matcher matcher = pattern.matcher(sql.toUpperCase());
        return matcher.find();
    }

    public static boolean isCallStatement(String sql) {
        sql = removeComments(sql);
        sql = removeWhitespace(sql);
        sql = getFirstKeyword(sql);

        // 判断是否为查询语句，检查第一个关键字是否匹配
        Pattern pattern = Pattern.compile("^(CALL)");
        Matcher matcher = pattern.matcher(sql.toUpperCase());
        return matcher.find();
    }

    public static boolean hasLimitStatement(String sql) {
        sql = removeComments(sql);
        sql = removeWhitespace(sql);
        String firstKey = getFirstKeyword(sql);
        if (firstKey.equalsIgnoreCase("SELECT")) {
            // 使用正则表达式匹配SQL语句的末尾，判断是否已经存在LIMIT子句
            Pattern pattern = Pattern.compile(".*\\bLIMIT\\b.*$");
            Matcher matcher = pattern.matcher(sql.toUpperCase());
            return matcher.find();
        } else {
            return true;
        }
    }

    public static boolean isIndependentCountStatement(String sql) {
        // 使用正则表达式匹配 SQL 语句中的 COUNT 关键字
        String regex = "\\bCOUNT\\b";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);

        // 检查是否有独立的 COUNT 语句
        if (matcher.find()) {
            // 检查是否在括号内，如果是，则不是独立的 COUNT 语句
            int openParenIndex = sql.indexOf('(');
            int closeParenIndex = sql.indexOf(')');
            if (openParenIndex != -1 && closeParenIndex != -1 && matcher.start() > openParenIndex && matcher.end() < closeParenIndex) {
                return false;
            }
            // 检查是否在 UNION 或 UNION ALL 语句中，如果是，则不是独立的 COUNT 语句
            int unionIndex = sql.indexOf("UNION");
            if (unionIndex != -1 && matcher.start() > unionIndex) {
                return false;
            }
            // 检查是否作为列名的一部分出现在 SQL 语句中，如果是，则不是独立的 COUNT 语句
            int countAsColumnIndex = sql.indexOf("COUNT(");
            if (countAsColumnIndex != -1 && matcher.start() > countAsColumnIndex) {
                return false;
            }
            return true;
        }
        return false;
    }

    public static boolean isExplainStatement(String sql) {
        sql = removeComments(sql);
        sql = removeWhitespace(sql);
        sql = getFirstKeyword(sql);

        // 判断是否为查询语句，检查第一个关键字是否匹配
        Pattern pattern = Pattern.compile("^(EXPLAIN)");
        Matcher matcher = pattern.matcher(sql.toUpperCase());
        return matcher.find();
    }


    public static boolean isModifyStatement(String sql) {
        sql = removeComments(sql);
        sql = removeWhitespace(sql);
        sql = getFirstKeyword(sql);

        // 判断是否为修改数据行语句，检查第一个关键字是否匹配
        Pattern pattern = Pattern.compile("^(INSERT|UPDATE|DELETE|MERGE)");
        Matcher matcher = pattern.matcher(sql.toUpperCase());
        return matcher.find();
    }

    public static boolean isPatternStatement(String patternstr, String sql) {
        sql = removeComments(sql);
        sql = removeWhitespace(sql);
        sql = getFirstKeyword(sql);

        Pattern pattern = Pattern.compile(patternstr);
        Matcher matcher = pattern.matcher(sql.toUpperCase());
        return matcher.find();
    }

    public static SQLStmtType getStatementType(String sql) {
        if (isQueryStatement(sql)) {
            return SQLStmtType.QUERY;
        } else if (isCallStatement(sql)) {
            return SQLStmtType.CALL;
        } else if (isExplainStatement(sql)) {
            return SQLStmtType.EXPLAIN;
        } else if (isModifyStatement(sql)) {
            return SQLStmtType.MODIFY;
        } else {
            return SQLStmtType.OTHER;
        }
    }

    public static DbOperType getDbOperType(String sql) {
        Map<String, String> typeMap = DbOperType.getDbOperTypeList();
        AtomicReference<DbOperType> operType = new AtomicReference<>(DbOperType.OTHER);
        typeMap.entrySet().stream().forEach(entry -> {
            if(!"other".equals(entry.getKey())){
                if(isPatternStatement(entry.getValue(), sql)) {
                    operType.set(DbOperType.getDbOperType(entry.getKey()));
                }
            }
        });
        return operType.get();
    }

    public static List<String> getAllSQLStatements(String sql) {
        sql = removeComments(sql);
        sql = sql.trim();

        // 分割多个SQL语句
        String[] statements = sql.split(";");

        // 去除每个SQL语句的空格和回车
        List<String> result = new ArrayList<>();
        for (String statement : statements) {
            String trimmedStatement = removeWhitespace(statement);
            if (!trimmedStatement.isEmpty()) {
                result.add(trimmedStatement);
            }
        }

        return result;
    }

    public static String getCountStatements(String sql) {
        String alias = sql.contains("fanwei") ? "fanjunzi" : "fanwei";
        String countSql = String.join(" ", "SELECT", "count(*)", "FROM", "(", sql, ")", alias);
        return countSql;
    }

    public static List<String> getPageStatements(Driver driver, String sql, Integer page, Integer size) {
        List<String> result = new ArrayList<>();
        String alias = sql.contains("fanwei") ? "fanjunzi" : "fanwei";
        String countSql = String.join(" ", "SELECT", "count(*)", "FROM", "(", sql, ")", alias);
        String querySql;
        if (driver instanceof DmDriver) {
            querySql = String.join(" ", "SELECT", alias + ".*", "FROM", "(", sql, ")", alias, "OFFSET", (page - 1) + "", "LIMIT", size + "");

        }else if(driver instanceof com.mysql.cj.jdbc.Driver){
            querySql = String.join(" ", "SELECT", alias + ".*", "FROM", "(", sql, ")", alias, "LIMIT", ((page - 1) * size) + "", (((page - 1) * size) + size) + "");

        }else if(driver instanceof org.postgresql.Driver){
            querySql = String.join(" ", "SELECT", alias + ".*", "FROM", "(", sql, ")", alias, "OFFSET", (page - 1) + "", "LIMIT", size + "");

        }else {
            querySql = String.join(" ", "SELECT", alias + ".*", "FROM", "(", sql, ")", alias, "OFFSET", (page - 1) + "", "LIMIT", size + "");
        }

        result.add(countSql);
        result.add(querySql);
        return result;
    }

    public static void main(String[] args) {
        String sql = "/* SELECT column1 FROM table1; */SELECT column1 FROM table1; --SELECT column1 FROM table1;\nINSERT INTO table2 (column1, column2) VALUES (1, 'Value');";
        List<String> statements = getAllSQLStatements(sql);

        System.out.println("Number of SQL statements: " + statements.size());
        for (String statement : statements) {
            System.out.println("SQL statement: " + statement);
            System.out.println("Is query statement: " + isQueryStatement(statement));
            System.out.println("Is modify statement: " + isModifyStatement(statement));
        }

        String sql2 = "SELECT * FROM table_name Limit 1;";
        if (hasLimitStatement(sql2)) {
            System.out.println("SQL语句以LIMIT子句结尾");
        } else {
            System.out.println("SQL语句不以LIMIT子句结尾");
        }

        String sql3 = "selelct  count(1) as count from t";
        System.out.println("Is count statement：" + isIndependentCountStatement(sql3));
    }


}
